﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using EvaluationSystem.Exceptions;
using System.Web.UI;

namespace EvaluationSystem.DAO
{
    public class Users
    {
        public int Id { get; set; }
        public string UserName { get;set;}
     //   public string Password { get; set; }
        public string Groups { get; set; }
        public string RoleName { get; set; }
    }

    public class StaffManager:DAO
    {
        const string SelectGroupCommand = " select [Id],[GroupName] from Groups ";
        const string SelectGroupByIdCommand = " select [Id],[GroupName] from Groups where [Id]=@Id ";
        const string SelectRoleCommand = " select [Id],[RoleName],[RoleDescription] from Roles ";
        const string SelectRoleByIdCommand = " select [Id],[RoleName],[RoleDescription] from Roles where [Id]=@Id ";
        const string SelectUserByUserName = " select [Id] from [Users] where [UserName]=@UserName ";
        const string SelectUserByUserNameExcpetMe = " select [Id] from [Users] where [UserName]=@UserName and [Id]<>@Id ";
        const string SaveStaffCommand = " insert into [Users] ([UserName], [Password], [Groups], [RoleId]) values (@UserName, @Password, @GroupId, @RoleId) ";
        const string UpdateStaffCommand = " update [Users] set [UserName]=@UserName, [Password]=@[Password], [Groups]=@Groups, [RoleId]=@RoleId where [Id]=@Id ";
        const string UpdateStaffNoPassowdCommand = " update [Users] set [UserName]=@UserName, [Groups]=@Groups, [RoleId]=@RoleId where [Id]=@Id ";
        const string SaveStaffNoPasswordCommand = " insert into [Users] ([UserName],  [Groups], [RoleId]) values (@UserName,  @GroupId, @RoleId) ";
        const string SelectAllUsersCommand = " select  [Users].[Id], [UserName],[Groups],[RoleName],[RoleId]  from [Users], [Roles] where [Users].[RoleId] = [Roles].[Id] ";
        const string SelectedUserById = " select [Users].[Id], [UserName],[Groups], [RoleId] from [Users] where [Id]=@Id ";
        const string GetUsersCountCommand = " select count([Id]) from [Users] ";
        const string DeleteUserByIdCommand = " delete [Users] where [Id]=@Id ";

        public int GetUsersCount(Page page, bool isRefresh = false)
        {
            if (page.Session[Constants.UsersCount] != null && !isRefresh)
            {
                return (int)page.Session[Constants.UsersCount];
            }
            else
            {
                page.Session[Constants.UsersCount] = ExcuteCommandDataTable(GetUsersCountCommand, null).Rows[0][0];
                return (int)page.Session[Constants.UsersCount];
            }
        }

        public bool DeleteUserById(string Id)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>(1);
            parameters["Id"] = Id;
            return ExcuteCommandNonQuery(DeleteUserByIdCommand, parameters) > 0;
        }

        public DataTable GetAllUsers()
        {
            return ExcuteCommandDataTable(SelectAllUsersCommand, null);
        }

        public DataTable GetUserById(string userId)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>(1);
            parameters["Id"] = userId;
            return ExcuteCommandDataTable(SelectedUserById, parameters);
        }

        public DataTable GetGroups()
        {
            return ExcuteCommandDataTable(SelectGroupCommand, null);
        }

        public DataTable GetGroupsById(string roleId)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>(1);
            parameters["Id"] = roleId;
            return ExcuteCommandDataTable(SelectGroupCommand, parameters);
        }

        public DataTable GetRoles()
        {
            return ExcuteCommandDataTable(SelectRoleCommand, null);
        }

        public DataTable GetRolesById(string roleId)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>(1);
            parameters["Id"] = roleId;
            return ExcuteCommandDataTable(SelectRoleCommand, parameters);
        }

        private bool CheckExistUser(string userName)
        { 
            Dictionary<string,object> parameters = new Dictionary<string,object>(1);
            parameters["UserName"] = userName;
            return ExcuteCommandDataTable(SelectUserByUserName, parameters).Rows.Count > 0;
        }

        private bool CheckExistUserExceptMe(string userName,string Id)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>(2);
            parameters["UserName"] = userName;
            parameters["Id"] = Id;
            return ExcuteCommandDataTable(SelectUserByUserNameExcpetMe, parameters).Rows.Count > 0;
        }

        public bool AddStaff(string userName, string password, string groupId, string roleId)
        {
            if (CheckExistUser(userName))
            {
                throw new InvalidUserException();
            }
            Dictionary<string, object> parameters = new Dictionary<string, object>(4);
            parameters["UserName"] = userName;
            parameters["Password"] = password;
            parameters["GroupId"] = groupId;
            parameters["RoleId"] = roleId;

            return ExcuteCommandNonQuery(String.IsNullOrEmpty(password)?SaveStaffNoPasswordCommand : SaveStaffCommand, parameters) > 0;
        }

        public bool UpdateStaffById(string id, string userName, string password, string groupName, string roleId)
        {
            if (CheckExistUserExceptMe(userName,id))
            {
                throw new InvalidUserException();
            }

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters["UserName"] = userName;
            parameters["Id"] = id;
            if (!String.IsNullOrEmpty(password))
            {
                parameters["Password"] = password;
            }
            parameters["Groups"] = groupName;
            parameters["RoleId"] = roleId;

            return ExcuteCommandNonQuery(String.IsNullOrEmpty(password) ? UpdateStaffNoPassowdCommand : UpdateStaffCommand, parameters) > 0;
        }

    }
}